﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using IdeaDomain.DomainLayer.Entities;
using IdeaDomain.DomainLayer.RepositoryInterfaces;
using IdeaDomain.InfrastructureLayer.DataEntities;
using IdeaDomain.InfrastructureLayer.DataManagers;
using IdeaDomain.InfrastructureLayer.Translator;
using UtilityComponent.DataFactory;

namespace IdeaDomain.InfrastructureLayer.Repositories
{
    public class IdeaRepository : IIdeaRepository
    {
        private readonly ColumnManager _columnManager;
        private readonly IdeaManager _ideaManager;

        private readonly IDataAccess _dataAccess = DataAccessFactory.CreateDataAccess();
        private readonly IdeaDomainModelDataEntities _mapper = new IdeaDomainModelDataEntities();


        /// <summary>
        /// Initialize a data access interface
        /// </summary>
        public IdeaRepository()
        {
            _dataAccess = DataAccessFactory.CreateDataAccess();

            _columnManager = new ColumnManager();
            _ideaManager = new IdeaManager();
        }

        /// <summary>
        /// Get all ideas by userId.
        /// </summary>
        /// <param name="userId"></param>
        /// <returns>Return idea list</returns>
        public List<Idea> GetIdeasByUser(int userId)
        {
            var ideaListResult = new List<Idea>();
            using (var connection = _dataAccess.CreateConnection())
            {
                connection.Open();
                var sqltrans = connection.BeginTransaction();
                var cmd = new SqlCommand { Connection = connection, Transaction = sqltrans };
                try
                {
                    var ideaList = _mapper.Map<List<Idea>>(_ideaManager.GetIdeasByUserId(cmd, userId));
                    foreach (var idea in ideaList)
                    {
                        idea.Columns = _mapper.Map<List<ColumnInIdea>>(_columnManager.GetColumnnsByIdeaId(cmd, idea.IdeaId));
                        ideaListResult.Add(idea);
                    }
                    sqltrans.Commit();
                }
                catch (Exception ex)
                {
                    sqltrans.Rollback();
                }
                connection.Close();
            }
            return ideaListResult;
        }

        /// <summary>
        /// Get idea by ideaid.
        /// </summary>
        /// <param name="ideaId"></param>
        /// <returns>
        /// Return an idea object
        /// </returns>
        public Idea GetIdeaById(int ideaId)
        {
            var idea = new Idea();
            using (var connection = _dataAccess.CreateConnection())
            {
                connection.Open();
                var sqltrans = connection.BeginTransaction();
                var cmd = new SqlCommand { Connection = connection, Transaction = sqltrans };
                try
                {
                    idea = _mapper.Map<Idea>(_ideaManager.GetIdeaById(cmd, ideaId));
                    idea.Columns = _mapper.Map<List<ColumnInIdea>>(_columnManager.GetColumnnsByIdeaId(cmd, ideaId));
                    foreach (var column in idea.Columns)
                    {
                        if (column.DataTypeId == 5 && column.ReferedIdeaId > 0)
                        {
                            var refIdea = _mapper.Map<Idea>(_ideaManager.GetIdeaById(cmd, column.ReferedIdeaId));
                            column.ReferedIdeaIdName = refIdea.IdeaName;
                        }
                        else
                        {
                            column.ReferedIdeaIdName = "";
                        }
                    }
                    sqltrans.Commit();
                }
                catch (Exception ex)
                {
                    sqltrans.Rollback();
                }
                connection.Close();
            }
            return idea;
        }

        /// <summary>
        /// Create an idea with its columns under a transaction.
        /// </summary>
        /// <param name="idea"></param>
        /// <returns></returns>
        public Idea CreateIdea(Idea idea)
        {
            if (idea == null) return null;
            using (var connection = _dataAccess.CreateConnection())
            {
                connection.Open();
                var sqltrans = connection.BeginTransaction();
                var cmd = new SqlCommand { Connection = connection, Transaction = sqltrans };
                try
                {
                    idea.IdeaId = _ideaManager.CreateIdea(cmd, _mapper.Map<IdeaDE>(idea));
                    if (idea.Columns != null)
                    {
                        var columnListNew = new List<ColumnInIdea>();
                        foreach (var column in idea.Columns)
                        {
                            column.IdeaId = idea.IdeaId;
                            column.ColumnId = _columnManager.CreateColumn(cmd, _mapper.Map<ColumnDE>(column));
                            columnListNew.Add(column);
                        }
                        idea.Columns = columnListNew;
                    }
                    sqltrans.Commit();
                }
                catch (Exception ex)
                {
                    sqltrans.Rollback();
                }
                connection.Close();
            }
            return idea;
        }

        /// <summary>
        /// Update an idea.
        /// </summary>
        /// <param name="idea"></param>
        /// <returns></returns>
        public bool UpdateIdea(Idea idea)
        {
            var result = false;
            if (idea == null) return false;
            using (var connection = _dataAccess.CreateConnection())
            {
                connection.Open();
                var sqltrans = connection.BeginTransaction();
                var cmd = new SqlCommand { Connection = connection, Transaction = sqltrans };
                try
                {
                    result = _ideaManager.UpdateIdea(cmd, _mapper.Map<IdeaDE>(idea));
                    if (idea.Columns != null && result)
                    {
                        var ideaFromDb = this.GetIdeaById(idea.IdeaId);
                        var columnsList = new List<ColumnInIdea>();
                        foreach (var column in idea.Columns)
                        {
                            column.IdeaId = idea.IdeaId;
                            column.ColumnId = _columnManager.CreateColumn(cmd, _mapper.Map<ColumnDE>(column));

                            if (ideaFromDb.Columns != null)
                            {
                                ideaFromDb.Columns.Add(column);
                            }
                            else
                            {
                                columnsList.Add(column);
                                ideaFromDb.Columns = columnsList;
                            }
                        }
                    }
                    sqltrans.Commit();
                }
                catch (Exception ex)
                {
                    sqltrans.Rollback();
                }
                connection.Close();
            }
            return result;
        }

        /// <summary>
        /// Delete an idea by ideaid.
        /// </summary>
        /// <param name="ideaId"></param>
        /// <returns></returns>
        public bool DeleteIdeaById(int ideaId)
        {
            var result = false;
            using (var connection = _dataAccess.CreateConnection())
            {
                connection.Open();
                var cmd = new SqlCommand { Connection = connection };
                var referenceResult = _columnManager.CheckReference(cmd, ideaId);
                if (!referenceResult)
                {
                    result = _ideaManager.DeleteIdeaById(cmd, ideaId);
                }
                connection.Close();
            }
            return result;
        }

        /// <summary>
        /// Validate Idea Name Duplication.
        /// </summary>
        /// <param name="idea"></param>
        /// <returns></returns> 
        public bool ValidateIdeaNameDuplication(Idea idea)
        {
            var result = false;
            using (var connection = _dataAccess.CreateConnection())
            {
                connection.Open();
                var cmd = new SqlCommand { Connection = connection };
                var ideaResult = _mapper.Map<Idea>(_ideaManager.GetIdeaByName(cmd, idea.IdeaName, idea.UserId));
                if (ideaResult != null && ideaResult.IdeaId != idea.IdeaId)
                {
                    result = true;
                }
            }
            return result;
        }

        /// <summary>
        /// Get a column by columnid.
        /// </summary>
        /// <param name="columnId"></param>
        /// <returns></returns>
        public ColumnInIdea GetColumnById(int columnId)
        {
            ColumnInIdea column;
            using (var connection = _dataAccess.CreateConnection())
            {
                connection.Open();
                var cmd = new SqlCommand { Connection = connection };
                column = _mapper.Map<ColumnInIdea>(_columnManager.GetColumnById(cmd, columnId));
                connection.Close();
            }
            return column;
        }

        /// <summary>
        /// Update a column by column entity.
        /// </summary>
        /// <param name="column"></param>
        /// <returns></returns>
        /// ML
        public bool UpdateColumn(ColumnInIdea column)
        {
            bool result;
            using (var connection = _dataAccess.CreateConnection())
            {
                connection.Open();
                var sqltrans = connection.BeginTransaction();
                var cmd = new SqlCommand { Connection = connection, Transaction = sqltrans };
                result = _columnManager.DeleteColumnById(cmd, column.ColumnId);
                if (result)
                {
                    column.ColumnId = _columnManager.CreateColumn(cmd, _mapper.Map<ColumnDE>(column));
                }
                sqltrans.Commit();
                connection.Close();
            }
            return result;
        }

        /// <summary>
        /// Delete a column by columnid.
        /// </summary>
        /// <param name="columnId"></param>
        /// <returns></returns>
        public bool DeleteColumnById(int columnId)
        {
            bool result;
            using (var connection = _dataAccess.CreateConnection())
            {
                connection.Open();
                var cmd = new SqlCommand { Connection = connection };
                result = _columnManager.DeleteColumnById(cmd, columnId);
                connection.Close();
            }
            return result;
        }

        /// <summary>
        /// Gets the ref columns by column id.
        /// </summary>
        /// <param name="columnId">The column id.</param>
        /// <returns></returns>
        public List<ColumnInIdea> GetRefColumnsByColumnId(int columnId)
        {
            var columns = new List<ColumnInIdea>();
            using (var connection = _dataAccess.CreateConnection())
            {
                connection.Open();
                var cmd = new SqlCommand { Connection = connection };
                var dt = _columnManager.GetRefColumnsByColumnId(cmd, columnId);
                columns.AddRange(from DataRow row in dt.Rows select _mapper.Map<ColumnInIdea>(_columnManager.GetColumnById(cmd, Convert.ToInt32(row["RefColumnId"]))));
            }
            return columns;
        }


    }
}
